import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import dws_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def daily_fridge_status(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(dws_bucket,table_name, date, False)
    sql = f'''insert into dws.daily_fridge_status
                (select 
                    sn,material,
                    avg(coldroom_diff) as  coldroom_diffmean, max(coldroom_diff)  as coldroom_diffmax, min(coldroom_diff) as coldroom_diffmin,
                    avg(freezeroom_diff) as freezeroom_diffmean,max(freezeroom_diff) as freezeroom_diffmax, min(freezeroom_diff) as freezeroom_diffmin,
                    avg(chang_diff) as chang_diffmean, max(chang_diff) as chang_diffmax, min(chang_diff) as chang_diffmin,
                    avg(coldroom_diff_abs) as  coldroom_diffabs_mean, max(coldroom_diff_abs)  as coldroom_diffabs_max, min(coldroom_diff_abs) as coldroom_diffabs_min,
                    avg(freezeroom_diff_abs) as freezeroom_diffabs_mean,max(freezeroom_diff_abs) as freezeroom_diffabs_max, min(freezeroom_diff_abs) as freezeroom_diffabs_min,
                    avg(chang_diff_abs) as chang_diffabs_mean, max(chang_diff_abs) as chang_diffabs_max, min(chang_diff_abs) as chang_diffabs_min,
                    avg(coldRoomSensorTempreture) as coldtempreture_mean, max(coldRoomSensorTempreture) as coldtempreture_max, min(coldRoomSensorTempreture) as coldtempreture_min,
                    avg(freezeRoomSensorTempreture) as freezetempreture_mean , max(freezeRoomSensorTempreture) as freezetempreture_max , min(freezeRoomSensorTempreture) as freezetempreture_min,
                    avg(changingSensorTempreture) as changtempreture_mean , max(changingSensorTempreture) as changtempreture_max , min(changingSensorTempreture) as changtempreture_min,
                    avg(evnSensorTempreture) as evnsensortempreture_mean , max(evnSensorTempreture) as evnsensortempreture_max , min(evnSensorTempreture) as evnsensortempreture_min,
                    avg(coldRoomEvaporationSensorTempreture) as coldvaporationtempreture_mean , max(coldRoomEvaporationSensorTempreture) as coldvaporationtempreture_max , min(coldRoomEvaporationSensorTempreture) as coldvaporationtempreture_min,
                    avg(changingRoomEvaporationSensorTempreture) as chanvaporationempreture_mean , max(changingRoomEvaporationSensorTempreture) as chanvaporationempreture_max, min(changingRoomEvaporationSensorTempreture) as chanvaporationempreture_min,
                    avg(freezeRoomEvaporationSensorTempreture) as freezeevaporationtempreture_mean, max(freezeRoomEvaporationSensorTempreture) as freezeevaporationtempreture_max, min(freezeRoomEvaporationSensorTempreture) as freezeevaporationtempreture_min,
                    avg(humiditySensorValue) as humidity_mean, max(humiditySensorValue) as humidity_max, min(humiditySensorValue) as humidity_min,
                    max(timestamp) as lasttime, 
                    count(1) as record_count,
                    p_date
                from
                    (
                        select      
                            sn, material, 
                            (coldRoomTargetTempreture-coldRoomSensorTempreture) as coldroom_diff,
                            (freezeRoomTargetTempreture - freezeRoomSensorTempreture) as freezeroom_diff,
                            (changingRoomTargetTempreture - changingSensorTempreture) as chang_diff,
                            abs(coldRoomTargetTempreture-coldRoomSensorTempreture) as coldroom_diff_abs,
                            abs(freezeRoomTargetTempreture - freezeRoomSensorTempreture) as freezeroom_diff_abs,
                            abs(changingRoomTargetTempreture - changingSensorTempreture) as chang_diff_abs,
                            coldRoomSensorTempreture,
                            freezeRoomSensorTempreture,
                            changingSensorTempreture,
                            evnSensorTempreture,
                            coldRoomEvaporationSensorTempreture,
                            changingRoomEvaporationSensorTempreture,
                            freezeRoomEvaporationSensorTempreture,
                            humiditySensorValue,
                            timestamp,
                            p_date
                        from 
                            ods.iot_fridge 
                        where
                            date(p_date) = (cast ('{date}' as date)- interval '1' day)    
                    ) t
                group by
                    sn,material,p_date
                )
                 '''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        daily_fridge_status(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

